import base64
import io
import pandas as pd
import dash  # 加上这一行
from dash import Input, Output, State, dcc,html
from flask import current_app as app
from models import db, MaintainRecord,Organization


import os
from datetime import datetime

def register_maintain_callbacks(dash_app):
    @dash_app.callback(
        [
            Output('maintain-record-table', 'data'),
            Output('maintain-org-filter', 'value'),
            Output('maintain-status-filter', 'value'),
            Output('maintain-date-range', 'start_date'),
            Output('maintain-date-range', 'end_date'),
            Output('maintain-product-search', 'value'),
            Output('maintain-product-id-filter', 'value'), # <-- 添加产品ID筛选框的输出
        ],
        [
            Input('maintain-filter-btn', 'n_clicks'),
            Input('maintain-reset-btn', 'n_clicks'),
            Input('maintain-org-filter', 'value'),
            Input('maintain-status-filter', 'value'),
            Input('maintain-date-range', 'start_date'),
            Input('maintain-date-range', 'end_date'),
            Input('maintain-product-search', 'value'),
            Input('maintain-product-id-filter', 'value'), # <-- 添加产品ID筛选框的输入
        ],
        prevent_initial_call=False
    )
    def update_maintain_table(filter_clicks, reset_clicks, org, status, start_date, end_date, search, product_id_filter_value):
        ctx = dash.callback_context
        reset = ctx.triggered and ctx.triggered[0]['prop_id'].startswith('maintain-reset-btn')
        if reset:
            org = None
            status = None
            start_date = None
            end_date = None
            search = None
            product_id_filter_value = None # <-- 重置时清空产品ID筛选值
        
        # 从session中获取当前用户的org_code
        from flask import session
        user_org_code = session.get('user_org_code')
        
        query = db.session.query(MaintainRecord).filter(MaintainRecord.is_active == True)
        
        # 如果用户不是HPQWJW（黄浦区卫健委），则只能查看自己机构的设备
        if user_org_code and user_org_code != 'HPQWJW':
            # 先通过 user_org_code 查询 Organization 表获取 short_name_c
            org_obj = Organization.query.filter_by(org_code=user_org_code).first()
            if org_obj and org_obj.short_name_c:
                query = query.filter(MaintainRecord.org_name == org_obj.short_name_c)
        
        if org:
            # print(f"过滤机构: {org}")
            query = query.filter(MaintainRecord.org_name == org)
        if status:
            # print(f"过滤状态: {status}")
            query = query.filter(MaintainRecord.completion_status == status)
        if start_date:
            # print(f"过滤开始日期: {start_date}")
            query = query.filter(MaintainRecord.scheduled_date >= start_date)
        if end_date:
            # print(f"过滤结束日期: {end_date}")
            query = query.filter(MaintainRecord.scheduled_date <= end_date)

        # 添加产品ID过滤逻辑
        if product_id_filter_value:
            try:
                # 尝试将输入转换为整数进行精确匹配
                product_id_int = int(product_id_filter_value)
                query = query.filter(MaintainRecord.product_id == product_id_int)
            except (ValueError, TypeError):
                # 如果转换失败，可能是输入了非数字，可以忽略此过滤或给出提示
                print(f"产品ID筛选值 '{product_id_filter_value}' 无效，请输入数字。", flush=True)
                # 或者，如果您希望支持模糊匹配，但这取决于 product_id 的实际数据类型和存储方式和数据库类型
                # query = query.filter(MaintainRecord.product_id.cast(db.String).contains(str(product_id_filter_value)))
                pass # 忽略无效输入，不进行产品ID过滤


        if search: # 原有的产品名称/编号搜索逻辑
            # print(f"搜索: {search}")
            query = query.filter(
                (MaintainRecord.product_name.contains(search)) |
                (MaintainRecord.product_code.contains(search))
            )

        records = query.order_by(MaintainRecord.scheduled_date.desc()).all()
        # print(f"查到记录数: {len(records)}")
        data = []
        for r in records:
            # print(f"记录: id={r.id}, product_name={r.product_name}, org_name={r.org_name}")
            data.append({
                "product_id": r.product_id,
                "product_name": r.product_name,
                "product_code": r.product_code,
                "org_name": r.org_name,
                "dept_name": r.dept_name,
                "scheduled_date": r.scheduled_date.strftime("%Y-%m-%d") if r.scheduled_date else "",
                "actual_date": r.actual_date.strftime("%Y-%m-%d") if r.actual_date else "",
                "maintenance_type": r.maintenance_type,
                "maintenance_content": r.maintenance_content,
                "maintenance_result": r.maintenance_result,
                "maintenance_person": r.maintenance_person,
                "maintenance_company": r.maintenance_company,
                "completion_status": r.completion_status,
                "remarks": r.remarks,
            })
        # print(f"返回前端的数据条数: {len(data)}")
        # 这里返回的Output数量需要和装饰器中的Output列表数量一致
        if reset:
            return data, None, None, None, None, None, None # <-- 增加一个 None 对应新的Output
        else:
            return data, org, status, start_date, end_date, search, product_id_filter_value # <-- 增加 product_id_filter_value 对应新的Output

    @dash_app.callback(
        Output('maintain-org-filter', 'options'),
        Input('maintain-record-table', 'data')  # 或用其它合适的Input，如页面加载信号
    )
    def update_org_options(_):
        # 从session中获取当前用户的org_code
        from flask import session
        user_org_code = session.get('user_org_code')
        print(f"当前用户org_code: {user_org_code}") # 添加调试信息
        
        # 如果用户不是HPQWJW（黄浦区卫健委），则只能看到自己机构的选项
        if user_org_code and user_org_code.upper() != 'HPQWJW':
            # 通过 user_org_code 查询 Organization 表获取 short_name_c
            org_obj = db.session.query(Organization).filter(Organization.org_code == user_org_code).first()
            if org_obj and org_obj.short_name_c:
                # 只返回用户所属机构的选项
                return [{"label": org_obj.short_name_c, "value": org_obj.short_name_c}]
                print(f"用户 {user_org_code} 只能看到机构: {org_obj.short_name_c}")
        else:
            # HPQWJW用户可以看到所有机构
            orgs = db.session.query(MaintainRecord.org_name).distinct().all()
            options = [{"label": o[0], "value": o[0]} for o in orgs if o[0]]
            print(f"HPQWJW用户可以看到所有机构: {len(options)}个")
            return options

    @dash_app.callback(
        Output('maintain-status-filter', 'options'),
        Input('maintain-record-table', 'data')
    )
    def update_status_options(_):
        statuses = db.session.query(MaintainRecord.completion_status).distinct().all()
        return [{"label": s[0], "value": s[0]} for s in statuses if s[0]]

    @dash_app.callback(
        [
            Output('upload-maintain-plan', 'contents', allow_duplicate=True),  
            Output('maintain-upload-dialog', 'displayed'),
            Output('maintain-upload-dialog', 'message'),
        ],
        [
            Input('upload-maintain-plan', 'contents'),
            State('upload-maintain-plan', 'filename'),
            State('maintain-record-table', 'data'),
        ],
        prevent_initial_call=True
    )
    def upload_maintain_plan(contents, filename, current_data):
        if contents is None:
            return current_data, False, ""
        print(f"收到上传文件: {filename}")
        # 新增：校验文件名
        if "黄浦区保养记录" not in filename:
            return current_data, True, "文件无效，无法上传。"
        import pandas as pd
        import numpy as np

        def safe_date(val):
            import pandas as pd
            import datetime
            if pd.isna(val):
                return None
            if isinstance(val, datetime.date):
                return val
            if isinstance(val, pd.Timestamp):
                return val.date()
            if isinstance(val, float) or isinstance(val, int):
                # 处理Excel日期序列号
                try:
                    return pd.to_datetime(val, unit='d', origin='1899-12-30').date()
                except Exception:
                    return None
            if isinstance(val, str):
                for fmt in ("%Y-%m-%d", "%Y/%m/%d", "%Y.%m.%d", "%Y-%m-%d %H:%M:%S"):
                    try:
                        return datetime.datetime.strptime(val, fmt).date()
                    except Exception:
                        continue
                try:
                    # 尝试用pandas解析
                    return pd.to_datetime(val).date()
                except Exception:
                    return None
            return None

        content_type, content_string = contents.split(',')
        decoded = base64.b64decode(content_string)
        try:
            # 1. 上传前先导出现有数据
            backup_records = db.session.query(MaintainRecord).all()
            backup_data = []
            for r in backup_records:
                backup_data.append({
                    "product_id": r.product_id,
                    "product_name": r.product_name,
                    "product_code": r.product_code,
                    "org_name": r.org_name,
                    "dept_name": r.dept_name,
                    "scheduled_date": r.scheduled_date.strftime("%Y-%m-%d") if r.scheduled_date else "",
                    "actual_date": r.actual_date.strftime("%Y-%m-%d") if r.actual_date else "",
                    "maintenance_type": r.maintenance_type,
                    "maintenance_content": r.maintenance_content,
                    "maintenance_result": r.maintenance_result,
                    "maintenance_person": r.maintenance_person,
                    "maintenance_company": r.maintenance_company,
                    "completion_status": r.completion_status,
                    "remarks": r.remarks,
                })
            if backup_data:
                
                df_backup = pd.DataFrame(backup_data)
                now_str = datetime.now().strftime("%Y%m%d_%H%M%S")
                backup_filename = f"黄浦区保养记录表备份{now_str}.xlsx"
                # 保存到用户下载目录
                user_download_dir = os.path.expanduser("~\\Downloads")
                backup_path_download = os.path.join(user_download_dir, backup_filename)
                df_backup.to_excel(backup_path_download, index=False)
                # 保存到项目 backup\Maintain 目录
                backup_dir = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(__file__))), "backup", "Maintain")
                os.makedirs(backup_dir, exist_ok=True)
                backup_path_project = os.path.join(backup_dir, backup_filename)
                df_backup.to_excel(backup_path_project, index=False)
            # 2. 清空MaintainRecord表
            db.session.query(MaintainRecord).delete()
            db.session.commit()
            # 3. 读取新Excel并写入
            df = pd.read_excel(io.BytesIO(decoded))
            print(f"Excel读取成功，行数: {len(df)}")
            # 假设Excel列名与MaintainRecord字段一致，或做适当映射
            for idx, row in df.iterrows():
                # 处理日期字段
                scheduled_date = row.get('scheduled_date', None) or row.get('计划保养日期', None)
                actual_date = row.get('actual_date', None) or row.get('实际保养日期', None)
                print(f"p185 获取日期字段: scheduled_date={scheduled_date}, actual_date={actual_date}")   
                # 使用 safe_date 处理日期字段
                scheduled_date = safe_date(row.get('scheduled_date', None) or row.get('计划保养日期', None))
                actual_date = safe_date(row.get('actual_date', None) or row.get('实际保养日期', None))

                # 转换为date对象
                if isinstance(scheduled_date, str):
                    try:
                        scheduled_date = datetime.strptime(scheduled_date, "%Y-%m-%d").date()
                    except Exception:
                        scheduled_date = None
                elif isinstance(scheduled_date, pd.Timestamp):
                    scheduled_date = scheduled_date.date()
                if isinstance(actual_date, str):
                    try:
                        actual_date = datetime.strptime(actual_date, "%Y-%m-%d").date()
                    except Exception:
                        actual_date = None
                elif isinstance(actual_date, pd.Timestamp):
                    actual_date = actual_date.date()
                print(f"p205 处理日期字段: scheduled_date={scheduled_date}, actual_date={actual_date}")    
                record = MaintainRecord(
                    product_id = row.get('product_id', '') or row.get('产品id', ''),
                    product_name = row.get('product_name', '') or row.get('产品名称', ''),
                    product_code = row.get('product_code', '') or row.get('产品编号', ''),
                    org_name = row.get('org_name', '') or row.get('所属机构', ''),
                    dept_name = row.get('dept_name', '') or row.get('所属科室', ''),
                    scheduled_date = scheduled_date,
                    actual_date = actual_date,
                    maintenance_type = row.get('maintenance_type', '') or row.get('保养类型', ''),
                    maintenance_content = row.get('maintenance_content', '') or row.get('保养内容', ''),
                    maintenance_result = row.get('maintenance_result', '') or row.get('保养结果', ''),
                    maintenance_person = row.get('maintenance_person', '') or row.get('保养人员', ''),
                    maintenance_company = row.get('maintenance_company', '') or row.get('保养公司', ''),
                    completion_status = row.get('completion_status', '') or row.get('完成状态', ''),
                    remarks = row.get('remarks', '') or row.get('备注', ''),
                    is_active = True
                )
                db.session.add(record)
            db.session.commit()
            print("批量写入数据库成功")
            # 重新查询数据库，刷新表格
            records = db.session.query(MaintainRecord).filter(MaintainRecord.is_active == True).order_by(MaintainRecord.scheduled_date.desc()).all()
            data = []
            for r in records:
                data.append({
                    "product_id": r.product_id,
                    "product_name": r.product_name,
                    "product_code": r.product_code,
                    "org_name": r.org_name,
                    "dept_name": r.dept_name,
                    "scheduled_date": r.scheduled_date.strftime("%Y-%m-%d") if r.scheduled_date else "",
                    "actual_date": r.actual_date.strftime("%Y-%m-%d") if r.actual_date else "",
                    "maintenance_type": r.maintenance_type,
                    "maintenance_content": r.maintenance_content,
                    "maintenance_result": r.maintenance_result,
                    "maintenance_person": r.maintenance_person,
                    "maintenance_company": r.maintenance_company,
                    "completion_status": r.completion_status,
                    "remarks": r.remarks,
                })
            return None, True, "上传文件成功！请点击筛选按钮刷新页面"
        except Exception as e:
            print(f"上传解析或写入数据库失败: {e}")
            return current_data, True, f"上传文件失败: {e}"

    

    @dash_app.callback(
        Output('download-maintain-excel', 'data'),
        Input('export-maintain-excel', 'n_clicks'),
        State('maintain-record-table', 'data'),
        prevent_initial_call=True
    )
    def export_maintain_excel(n_clicks, table_data):
        if n_clicks and table_data:
            df = pd.DataFrame(table_data)
            now_str = datetime.now().strftime("%Y%m%d_%H%M%S")
            backup_filename = f"黄浦区保养记录表导出{now_str}.xlsx"
            return dcc.send_data_frame(df.to_excel, backup_filename, index=False)
        return None

    layout = html.Div([
        dcc.ConfirmDialog(
            id='maintain-upload-dialog',
            message=''
        ),
    ])